<?php 
/**
 *客户管理
*/

namespace app\api\controller;

use think\facade\Db;
use think\facade\Cache;
use think\facade\Log;
use app\api\controller\Base;


class Finance extends Base {


	/*入款结算*/
	function depositSettlement(){
      
        if (!$this->request->isAjax()){
			return $this->display('record/finance/depositSettlement');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$customer_idcard= $this->request->param('customer_idcard', '', 'strip_tags,trim');
			$customer_passort = $this->request->param('customer_passort', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
            if(!empty($customer_idcard)){
                $where[]= ['customer_idcard','like','%'.$customer_idcard.'%'];
            }
            if(!empty($customer_passort)){
                $where[] = ['customer_passort','like','%'.$customer_passort.'%'];
            }
            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$count=Db::name('deposit_settlement')->where($where)->count();
				$list =  Db::name('deposit_settlement')->where($where)->limit($start,$end)->order('created_time desc')->select()->toArray();
				if(!empty($list)){
					$customer_ids=array_column($list,'customer_id');
					$customer_list= Db::name('customer')->where('id','in',$customer_ids)->column('id,first_name,last_name,mobile','id');
					foreach ($list as $key => $value) {
						$list[$key]['full_name']=$customer_list[$value['customer_id']]['last_name'].$customer_list[$value['customer_id']]['first_name'];
						$list[$key]['mobile']=$customer_list[$value['customer_id']]['mobile'];
						$list[$key]['created_time_text']=date('Y-m-d',$value['created_time']);
						$list[$key]['amount']=sprintf('%d',$value['amount']);
					}
				}
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}

	
	/*绩效管理*/
	function performanceManage(){
      
        if (!$this->request->isAjax()){
			return $this->display('record/finance/performanceManage');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$id_card= $this->request->param('id_card', '', 'strip_tags,trim');
			$director_id= $this->request->param('director_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			// $end_time = $this->request->param('end_time', '', 'strip_tags,trim');

			$where[]=['status','=',7];
			$where[]=['is_jx_deal','=',2];
			$where[]=['director_id','>',0];

			//业务id
			$director_list=Db::name('user')->where('group_id','=',config('my.director_group_id'))->column('user_id');
			$director_list=array_merge($director_list);	
			
			$where[]=['director_id','in',$director_list];

            if(!empty($id_card)){
                $where[]= ['id_card','like','%'.$id_card.'%'];
            }
            // if(!empty($director_id)){
            //     $where[]= ['director_id','=',$director_id];
            // }
            if(!empty($start_time)){	
            	$start_date=$start_time.'-01 00:00:00';
            	$end_date=get_lastday($start_time).' 23:59:59';
				$where[]=['create_time','>=',strtotime($start_date)];
				$where[]=['create_time','<=',strtotime($end_date)];
            }
            // if(!empty($end_time)){
            //   	$end_time.=" 23:59:59";
            //   	$where[]=['create_time','<=',strtotime($end_time)];
            // }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$count=Db::name('customer')->where($where)->count();
				$list =  Db::name('customer')->where($where)->limit($start,$end)->order('create_time desc')->select()->toArray();
				// $list =  Db::name('customer')->where($where)->limit($limit)->order('create_time desc')->fetchSql(true)->select();
				// echo $list;exit();
				if(!empty($list)){
					foreach ($list as $key => $value) {
						$list[$key]['full_name']=$value['last_name'].$value['first_name'];
						// $list[$key]['work_year_month'] =$value['work_year'].'年'.$value['work_month'].'个月';
						$list[$key]['amount']=empty($value['introducer_id']) ? 300 : 100;
						$list[$key]['is_introducer']=!empty($value['introducer_id']) ? 1 : 2;
						$list[$key]['created_time_text']=date('Y-m-d',$value['create_time']);
					}
				}
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}



	/*业务所得*/
	function businessIncome(){
        if (!$this->request->isAjax()){
        	$director_list=Db::name('user')->field('user_id,name')->select()->toArray();
        	$this->view->assign('director_list',$director_list);
			return $this->display('record/finance/businessIncome');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$id_card= $this->request->param('id_card', '', 'strip_tags,trim');
			$director_id= $this->request->param('director_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');

			$where[]=['status','=',7];
			$where[]=['is_sd_deal','=',2];
            if(!empty($id_card)){
                $where[]= ['id_card','like','%'.$id_card.'%'];
            }
            if(!empty($director_id)){
                $where[]= ['director_id','=',$director_id];
            }
            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['create_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['create_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$count=Db::name('customer')->where($where)->count();
				$list =  Db::name('customer')->where($where)->limit($start,$end)->order('create_time desc')->select()->toArray();
	            $director_ids=array_column($list,'director_id');
	            $director_ids=array_unique($director_ids);
				$director_arr=Db::name('user')->where('user_id','in',$director_ids)->column('name','user_id');				
				if(!empty($list)){
					foreach ($list as $key => $value) {
						$list[$key]['full_name']=$value['last_name'].$value['first_name'];
						// $list[$key]['work_year_month'] =$value['work_year'].'年'.$value['work_month'].'个月';
						$list[$key]['created_time_text']=date('Y-m-d',$value['create_time']);
						$list[$key]['lbj_fee']=sprintf("%d",$value['lbj_fee']);
						$list[$key]['business_income_amount']=sprintf("%d",$value['lbj_fee']*0.2);
						$list[$key]['director_name']=empty($director_arr[$value['director_id']]) ? '' : $director_arr[$value['director_id']];
						$list[$key]['total_fee']=$value['id_card_fee']+$value['pass_port_fee']+$value['id_card_certification_fee']+$value['pass_port_certification_fee']+$value['id_card_lsg_fee']+$value['pass_port_lsg_fee'];
					}
				}
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}

	/*其他支出*/
	function otherExpense(){
      
        if (!$this->request->isAjax()){
        	$user_list=Db::name('user')->field('user_id,name')->select()->toArray();
        	$this->view->assign('user_list',$user_list);        	
			return $this->display('record/finance/otherExpense');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$subject = $this->request->param('subject', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$sub_subject = $this->request->param('sub_subject', '', 'strip_tags,trim');
			$user_id = $this->request->param('user_id', '', 'strip_tags,trim');

            if(!empty($subject)){
                $where[]= ['subject','=',$subject];
            }

            if(!empty($sub_subject)){
                $where[]= ['sub_subject','=',$sub_subject];
            }

            if(!empty($user_id)){
                $where[]= ['user_id','=',$user_id];
            }

            if(!empty($start_time)){
              	// $where[]=['created_ym','>=',str_replace('-','', $start_time)];
              	$where[]=['created_ym','=',str_replace('-','', $start_time)];
            }else{
            	$where[]=['created_ym','=',date('Ym')];
            }            

            // if(!empty($start_time)){
            //   	$start_time.=" 00:00:00";
            //   	$where[]=['created_time','>=',strtotime($start_time)];
            // }
            // if(!empty($end_time)){
            //   	$end_time.=" 23:59:59";
            //   	$where[]=['created_time','<=',strtotime($end_time)];
            // }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$count=Db::name('other_expense')->where($where)->count();
				$list =  Db::name('other_expense')->where($where)->limit($start,$end)->order('created_time desc')->select()->toArray();
				if(!empty($list)){
		            $user_ids=array_column($list,'user_id');
		            $user_ids=array_unique($user_ids);
					$user_arr=Db::name('user')->where('user_id','in',$user_ids)->column('name','user_id');
					foreach ($list as $key => $value) {
						$list[$key]['subject_name']=get_subject_name($value['subject']);
						$list[$key]['created_time_text']=date('Y-m-d',$value['created_time']);
						$list[$key]['name']=isset($user_arr[$value['user_id']]) ? trim($user_arr[$value['user_id']]) : '';
						$list[$key]['amount']=sprintf("%d",$value['amount']);
					}
				}
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*佣金明细报表*/
	function commissionReportDetail(){
      
        if (!$this->request->isAjax()){
        	$introducer_list=Db::name('introducer')->field('id,mobile,name')->select()->toArray();
        	$this->view->assign('introducer_list',$introducer_list);
			return $this->display('record/finance/commissionReportDetail');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$introducer_id = $this->request->param('introducer_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($introducer_id)){
                $where[]= ['introducer_id','=',$introducer_id];
            }

            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$result=[];
				$count=Db::name('commission_log')->where($where)->count();
				if(empty($export)){
					$list =  Db::name('commission_log')->where($where)->limit($start,$end)->order('created_time desc')->select()->toArray();
				}else{
					$list =  Db::name('commission_log')->field('introducer_id,amount,created_time')->where($where)->order('created_time desc')->select()->toArray();
				}
	            $introducer_ids=array_column($list,'introducer_id');
	            $introducer_ids=array_unique($introducer_ids);
				$introducer_arr=Db::name('introducer')->where('id','in',$introducer_ids)->column('name','id');
				if(!empty($list)){
					foreach ($list as $key => &$value) {
						// $list[$key]['introducer_name']=$introducer_arr[$value['introducer_id']];
						// $list[$key]['created_time_text']=date('Y-m-d',$value['created_time']);
						$value['introducer_name']=$introducer_arr[$value['introducer_id']];
						$value['amount']=sprintf("%d",$value['amount']);
						$value['created_time_text']=date('Y-m-d',$value['created_time']);
						if(!empty($export)){
							$result[]=[
								$value['introducer_name'],
								$value['amount'],
								$value['created_time_text'],
							];
						}
					}
					unset($value);
				}
				if(!empty($export)){
					$header=[
						'介绍人',
						'金额',
						'时间'
					];
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}
			}catch(\Exception $e){
				if(!empty($export)){
					return json(['code'=>1,'data'=>[],'msg'=>$e->getMessage()]);
				}
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*佣金月报表*/
	function commissionReportMonth(){
      
        if (!$this->request->isAjax()){
        	$introducer_list=Db::name('introducer')->field('id,mobile,name')->select()->toArray();
        	$this->view->assign('introducer_list',$introducer_list);        	
			return $this->display('record/finance/commissionReportMonth');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$introducer_id = $this->request->param('introducer_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($introducer_id)){
                $where[]= ['introducer_id','=',$introducer_id];
            }

            if(!empty($start_time)){
              	$where[]=['created_ym','=',str_replace('-','', $start_time)];
            }else{
            	$where[]=['created_ym','=',date('Ym')];
            }
            // if(!empty($end_time)){
            //   	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
            // }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$result=[];
				$count=Db::name('commission_log')->where($where)->group('introducer_id,created_ym')->count();
				if(empty($export)){
					$list =  Db::name('commission_log')->where($where)->field('introducer_id,created_ym,SUM(amount) AS amount')->group('introducer_id,created_ym')->limit($start,$end)->order('introducer_id asc')->select()->toArray();
				}else{
					$list =  Db::name('commission_log')->where($where)->field('introducer_id,created_ym,SUM(amount) AS amount')->group('introducer_id,created_ym')->order('introducer_id asc')->select()->toArray();
				}
				if(!empty($count)){
					$sum=Db::name('commission_log')->where($where)->sum('amount');
				}
				if(!empty($list)){
		            $introducer_ids=array_column($list,'introducer_id');
		            $introducer_ids=array_unique($introducer_ids);
					$introducer_arr=Db::name('introducer')->where('id','in',$introducer_ids)->column('name','id');
					foreach ($list as $key => &$value) {
						// $list[$key]['created_time_text']=date('Y-m-d',$value['created_time']);
						$value['introducer_name']=$introducer_arr[$value['introducer_id']];
						$value['amount']=sprintf("%d",$value['amount']);
						$value['sum']=isset($sum) ? $sum : '-';
						if(!empty($export)){
							$result[]=[
								$value['introducer_name'],
								$value['amount'],
							];
						}
					}
					unset($value);
				}

				if(!empty($export)){
					$header=[
						'介绍人',
						'金额',
					];
					if(!empty($result)){
						$result[]=[
							'合计',
							isset($sum) ? $sum : ''							
						];
					}
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}				
				

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}	

	/*佣金年报表*/
	function commissionReportYear(){
      
        if (!$this->request->isAjax()){
        	$introducer_list=Db::name('introducer')->field('id,mobile,name')->select()->toArray();
        	$this->view->assign('introducer_list',$introducer_list);         	
			return $this->display('record/finance/commissionReportYear');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$introducer_id = $this->request->param('introducer_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			// $end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($introducer_id)){
                $where[]= ['introducer_id','=',$introducer_id];
            }

            // if(!empty($start_time)){
            //   	$where[]=['created_y','>=',str_replace('-','', $start_time)];
            // }
            // if(!empty($end_time)){
            //   	$where[]=['created_y','<=',str_replace('-','', $end_time)];
            // }
            if(!empty($start_time)){
              	$where[]=['created_y','=',$start_time];
            }else{
            	$where[]=['created_y','=',date('Y')];
            }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$result=[];
				$count=Db::name('commission_log')->where($where)->group('introducer_id,created_y')->count();
				if(empty($export)){
					$list =  Db::name('commission_log')->where($where)->field('introducer_id,created_y,SUM(amount) AS amount')->group('introducer_id,created_y')->limit($start,$end)->order('introducer_id asc')->select()->toArray();					
				}else{
					$list =  Db::name('commission_log')->where($where)->field('introducer_id,created_y,SUM(amount) AS amount')->group('introducer_id,created_y')->order('introducer_id asc')->select()->toArray();					
				}
				if(!empty($count)){
					$sum=Db::name('commission_log')->where($where)->sum('amount');
				}
				if(!empty($list)){
		            $introducer_ids=array_column($list,'introducer_id');
		            $introducer_ids=array_unique($introducer_ids);
					$introducer_arr=Db::name('introducer')->where('id','in',$introducer_ids)->column('name','id');
					foreach ($list as $key => &$value) {
						// $list[$key]['created_time_text']=date('Y-m-d',$value['created_time']);
						$value['introducer_name']=$introducer_arr[$value['introducer_id']];
						$value['amount']=sprintf("%d",$value['amount']);
						$value['sum']=isset($sum) ? $sum : '-';
						if(!empty($export)){
							$result[]=[
								$value['introducer_name'],
								$value['amount'],
							];
						}						
					}
					unset($value);
				}

				if(!empty($export)){
					$header=[
						'介绍人',
						'金额',
					];
					if(!empty($result)){
						$result[]=[
							'合计',
							isset($sum) ? $sum : ''
						];
					}
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}

	/*业绩明细报表*/
	function performanceReportDetail(){
      
        if (!$this->request->isAjax()){
			return $this->display('record/finance/performanceReportDetail');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$director_id = $this->request->param('director_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($director_id)){
                $where[]= ['director_id','=',$director_id];
            }

            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$result=[];
				$count=Db::name('performance_log')->where($where)->count();
				if(empty($export)){
					$list =  Db::name('performance_log')->where($where)->limit($start,$end)->order('created_time desc')->select()->toArray();
				}else{
					$list =  Db::name('performance_log')->where($where)->order('created_time desc')->select()->toArray();
				}
				if(!empty($list)){
		            $director_ids=array_column($list,'director_id');
		            $director_ids=array_unique($director_ids);
					$director_arr=Db::name('user')->where('user_id','in',$director_ids)->column('name','user_id');
					foreach ($list as $key => &$value) {
						$value['created_time_text']=date('Y-m-d',$value['created_time']);
						$value['amount']=sprintf("%d",$value['amount']);
						$value['director_name']=empty($director_arr[$value['director_id']]) ? '' : $director_arr[$value['director_id']];
						if(!empty($export)){
							$result[]=[
								$value['director_name'],
								$value['amount'],
								$value['created_time_text']
							];
						}
					}
					unset($value);
				}

				if(!empty($export)){
					$header=[
						'承办人',
						'金额',
						'时间'
					];
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*业绩月报表*/
	function performanceReportMonth(){
      
        if (!$this->request->isAjax()){
			return $this->display('record/finance/performanceReportMonth');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$director_id = $this->request->param('director_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($director_id)){
                $where[]= ['director_id','=',$director_id];
            }

            if(!empty($start_time)){
              	// $where[]=['created_ym','>=',str_replace('-','', $start_time)];
              	$where[]=['created_ym','=',str_replace('-','', $start_time)];
            }else{
            	$where[]=['created_ym','=',date('Ym')];
            }
            // if(!empty($end_time)){
            //   	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
            // }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$result=[];
				$count=Db::name('performance_log')->where($where)->group('director_id,created_ym')->count();
				if(empty($export)){
					$list =  Db::name('performance_log')->where($where)->field('director_id,created_ym,SUM(amount) AS amount')->group('director_id,created_ym')->limit($start,$end)->order('director_id asc')->select()->toArray();
				}else{
					$list =  Db::name('performance_log')->where($where)->field('director_id,created_ym,SUM(amount) AS amount')->group('director_id,created_ym')->order('director_id asc')->select()->toArray();
				}
				if(!empty($count)){
					$sum=Db::name('performance_log')->where($where)->sum('amount');
				}			

				if(!empty($list)){
		            $director_ids=array_column($list,'director_id');
		            $director_ids=array_unique($director_ids);
					$director_arr=Db::name('user')->where('user_id','in',$director_ids)->column('name','user_id');
					foreach ($list as $key => &$value) {
						// $value['created_time_text']=date('Y-m-d',$value['created_time']);
						$value['director_name']=empty($director_arr[$value['director_id']]) ? '' : $director_arr[$value['director_id']];
						$value['amount']=sprintf("%d",$value['amount']);
						$value['sum']=isset($sum) ? $sum : '-';
						if(!empty($export)){
							$result[]=[
								$value['director_name'],
								$value['amount'],
							];
						}
					}
					unset($value);
				}

				if(!empty($export)){
					$header=[
						'承办人',
						'金额',
					];
					if(!empty($result)){
						$result[]=[
							'合计',
							isset($sum) ? $sum : ''							
						];
					}
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}																		
				
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}	

	/*业绩年报表*/
	function performanceReportYear(){
      
        if (!$this->request->isAjax()){
			return $this->display('record/finance/performanceReportYear');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$director_id = $this->request->param('director_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($director_id)){
                $where[]= ['director_id','=',$director_id];
            }

            if(!empty($start_time)){
              	// $where[]=['created_y','>=',str_replace('-','', $start_time)];
              	$where[]=['created_y','=',$start_time];
            }else{
            	$where[]=['created_y','=',date('Y')];
            }
            // if(!empty($end_time)){
            //   	$where[]=['created_y','<=',str_replace('-','', $end_time)];
            // }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$count=Db::name('performance_log')->where($where)->group('director_id,created_y')->count();
				if(empty($export)){
					$list =  Db::name('performance_log')->where($where)->field('director_id,created_y,SUM(amount) AS amount')->group('director_id,created_y')->limit($start,$end)->order('director_id asc')->select()->toArray();
				}else{
					$list =  Db::name('performance_log')->where($where)->field('director_id,created_y,SUM(amount) AS amount')->group('director_id,created_y')->order('director_id asc')->select()->toArray();
				}
				if(!empty($count)){
					$sum=Db::name('performance_log')->where($where)->sum('amount');
				}

				if(!empty($list)){
		            $director_ids=array_column($list,'director_id');
		            $director_ids=array_unique($director_ids);
					$director_arr=Db::name('user')->where('user_id','in',$director_ids)->column('name','user_id');
					foreach ($list as $key => &$value) {
						// $list[$key]['created_time_text']=date('Y-m-d',$value['created_time']);
						$value['director_name']=empty($director_arr[$value['director_id']]) ? '' : $director_arr[$value['director_id']];
						$value['amount']=sprintf("%d",$value['amount']);
						$value['sum']=isset($sum) ? $sum : '-';
						if(!empty($export)){
							$result[]=[
								$value['director_name'],
								$value['amount'],
							];
						}						
					}
					unset($value);
				}

				if(!empty($export)){
					$header=[
						'承办人',
						'金额',
					];
					if(!empty($result)){
						$result[]=[
							'合计',
							isset($sum) ? $sum : ''							
						];
					}
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}				

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}	


	/*收入明细报表*/
	function incomeReportDetail(){
      
        if (!$this->request->isAjax()){
			return $this->display('record/finance/incomeReportDetail');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
			$where[]=['type','=',2];
			$where[]=['subject','in',[90,100]];

            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$result=[];
				$count=Db::name('turnover')->where($where)->count();
				if(empty($export)){
					$list =  Db::name('turnover')->where($where)->limit($start,$end)->order('created_time desc')->select()->toArray();
				}else{
					$list =  Db::name('turnover')->where($where)->order('created_time desc')->select()->toArray();
				}
				if(!empty($list)){
					foreach ($list as $key => &$value) {
						$value['subject_name']=get_subject_name($value['subject']);
						$value['amount']=sprintf("%d",$value['amount']);
						$value['created_time_text']=date('Y-m-d',$value['created_time']);
						if(!empty($export)){
							$result[]=[
								$value['subject_name'],
								$value['amount'],
								$value['created_time_text']
							];
						}
					}
					unset($value);
				}

				if(!empty($export)){
					$header=[
						'科目',
						'金额',
						'时间'
					];
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*收入月报表*/
	function incomeReportMonth(){
      
        if (!$this->request->isAjax()){
			return $this->display('record/finance/incomeReportMonth');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
			$where[]=['type','=',2];
			$where[]=['subject','in',[90,100]];

            if(!empty($start_time)){
              	// $where[]=['created_ym','>=',str_replace('-','', $start_time)];
              	$where[]=['created_ym','=',str_replace('-','', $start_time)];
            }else{
            	$where[]=['created_ym','=',date('Ym')];
            }
            // if(!empty($end_time)){
            //   	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
            // }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$result=[];
				$count=Db::name('turnover')->where($where)->group('subject,created_ym')->count();
				if(empty($export)){
					$list =  Db::name('turnover')->where($where)->field('subject,created_ym,SUM(amount) AS amount')->group('subject,created_ym')->limit($start,$end)->order('subject asc')->select()->toArray();					
				}else{
					$list =  Db::name('turnover')->where($where)->field('subject,created_ym,SUM(amount) AS amount')->group('subject,created_ym')->order('subject asc')->select()->toArray();					
				}
				if(!empty($count)){
					$sum=Db::name('turnover')->where($where)->sum('amount');
				}

				if(!empty($list)){
					foreach ($list as $key => &$value) {
						$value['subject_name']=get_subject_name($value['subject']);
						$value['amount']=sprintf("%d",$value['amount']);
						$value['sum']=isset($sum) ? $sum : '-';
						if(!empty($export)){
							$result[]=[
								$value['subject_name'],
								$value['amount'],
							];
						}						
					}
					unset($value);
				}

				if(!empty($export)){
					$header=[
						'科目',
						'金额',
					];
					if(!empty($result)){
						$result[]=[
							'合计',
							isset($sum) ? $sum : ''							
						];
					}
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}


				
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}	

	/*收入年报表*/
	function incomeReportYear(){
      
        if (!$this->request->isAjax()){
			return $this->display('record/finance/incomeReportYear');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			// $end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
			$where[]=['type','=',2];
			$where[]=['subject','in',[90,100]];
            if(!empty($start_time)){
              	// $where[]=['created_ym','>=',str_replace('-','', $start_time)];
              	$where[]=['created_y','=',$start_time];
            }else{
            	$where[]=['created_y','=',date('Y')];
            }
            // if(!empty($end_time)){
            //   	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
            // }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$result=[];
				$count=Db::name('turnover')->where($where)->group('subject,created_y')->count();
				if(empty($export)){
					$list =  Db::name('turnover')->where($where)->field('subject,created_y,SUM(amount) AS amount')->group('subject,created_y')->limit($start,$end)->order('subject asc')->select()->toArray();					
				}else{
					$list =  Db::name('turnover')->where($where)->field('subject,created_y,SUM(amount) AS amount')->group('subject,created_y')->order('subject asc')->select()->toArray();
				}

				if(!empty($count)){
					$sum=Db::name('turnover')->where($where)->sum('amount');
				}

				if(!empty($list)){
					foreach ($list as $key => &$value) {
						$value['subject_name']=get_subject_name($value['subject']);
						$value['amount']=sprintf("%d",$value['amount']);
						$value['sum']=isset($sum) ? $sum : '-';
						if(!empty($export)){
							$result[]=[
								$value['subject_name'],
								$value['amount'],
							];
						}						
					}
					unset($value);
				}

				if(!empty($export)){
					$header=[
						'科目',
						'金额',
					];
					if(!empty($result)){
						$result[]=[
							'合计',
							isset($sum) ? $sum : ''							
						];
					}
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}
				

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*业务所得&代付费用明细*/
	function businessIncomeAndPayForReportDetail(){
      
        if (!$this->request->isAjax()){
			return $this->display('record/finance/businessIncomeAndPayForReportDetail');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['created_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['created_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$result=[];
				$count=Db::name('business_income')->where($where)->count();
				if(empty($export)){
					$list =  Db::name('business_income')->where($where)->limit($start,$end)->order('created_time desc')->select()->toArray();
				}else{
					$list =  Db::name('business_income')->where($where)->order('created_time desc')->select()->toArray();
				}
					
				if(!empty($list)){
					$customer_ids=array_column($list,'customer_id');
					$customer_list= Db::name('customer')->where('id','in',$customer_ids)->column('id,first_name,last_name,mobile','id');
					foreach ($list as $key => &$value) {
						$value['full_name']=$customer_list[$value['customer_id']]['last_name'].$customer_list[$value['customer_id']]['first_name'];
						$value['mobile']=$customer_list[$value['customer_id']]['mobile'];
						$value['created_time_text']=date('Y-m-d',$value['created_time']);
						$value['business_income_amount']=sprintf("%d",$value['business_income_amount']);
						$value['pay_for_amount']=sprintf("%d",$value['pay_for_amount']);
						if(!empty($export)){
							$result[]=[
								$value['customer_idcard'],
								$value['business_income_amount'],
								$value['pay_for_amount'],
								$value['created_time_text']
							];
						}						
					}
					unset($value);
				}					
				if(!empty($export)){
					$header=[
						'身份证',
						'业务所得',
						'代付费用',
						'时间',
					];
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}				

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}


	/*业务所得&代付费用月报表*/
	function  businessIncomeAndPayForReportMonth(){
      
        if (!$this->request->isAjax()){
			return $this->display('record/finance/businessIncomeAndPayForReportMonth');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			// $end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($start_time)){
              	// $where[]=['created_ym','>=',str_replace('-','', $start_time)];
              	$where[]=['created_ym','=',str_replace('-','', $start_time)];
            }else{
            	$where[]=['created_ym','=',date('Ym')];
            }
            // if(!empty($end_time)){
            //   	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
            // }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$result=[];
				$count=Db::name('business_income')->where($where)->group('customer_idcard,created_ym')->count();
				if(empty($export)){
					$list =  Db::name('business_income')->where($where)->field('customer_idcard,created_ym,SUM(business_income_amount) AS business_income_amount,SUM(pay_for_amount) AS pay_for_amount')->group('customer_idcard,created_ym')->limit($start,$end)->order('customer_idcard asc')->select()->toArray();					
				}else{
					$list =  Db::name('business_income')->where($where)->field('customer_idcard,created_ym,SUM(business_income_amount) AS business_income_amount,SUM(pay_for_amount) AS pay_for_amount')->group('customer_idcard,created_ym')->order('customer_idcard asc')->select()->toArray();					
				}

				if(!empty($count)){
					$sum=Db::name('business_income')->where($where)->field('SUM(business_income_amount) AS business_income_amount,SUM(pay_for_amount) AS pay_for_amount')->find();
				}

				if(!empty($list)){
					foreach ($list as $key => &$value) {
						// $list[$key]['subject_name']=get_subject_name($value['subject']);
						$value['income_sum']=isset($sum['business_income_amount']) ? sprintf("%d",$sum['business_income_amount']) : '-';
						$value['payfor_sum']=isset($sum['pay_for_amount']) ? sprintf("%d",$sum['pay_for_amount'])  : '-';
						$value['business_income_amount']=sprintf("%d",$value['business_income_amount']);
						$value['pay_for_amount']=sprintf("%d",$value['pay_for_amount']);
						if(!empty($export)){
							$result[]=[
								$value['customer_idcard'],
								$value['business_income_amount'],
								$value['pay_for_amount'],
							];
						}						
					}
					unset($value);
				}										
				if(!empty($export)){
					$header=[
						'身份证',
						'业务所得',
						'代付费用',
					];
					if(!empty($result)){
						$result[]=[
							'合计',
							isset($sum['business_income_amount']) ? sprintf("%d",$sum['business_income_amount']) : '',
							isset($sum['pay_for_amount']) ? sprintf("%d",$sum['pay_for_amount']) : '',
						];
					}
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}				

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}	

	/*业务所得&代付费用年报表*/
	function businessIncomeAndPayForReportYear(){
      
        if (!$this->request->isAjax()){
			return $this->display('record/finance/businessIncomeAndPayForReportYear');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$export = $this->request->param('export', '', 'strip_tags,trim');
            if(!empty($start_time)){
              	// $where[]=['created_y','>=',str_replace('-','', $start_time)];
              	$where[]=['created_y','=',$start_time];
            }else{
            	$where[]=['created_y','=',date('Y')];
            }
            // if(!empty($end_time)){
            //   	$where[]=['created_y','<=',str_replace('-','', $end_time)];
            // }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);
			try{
				$result=[];
				$count=Db::name('business_income')->where($where)->group('customer_idcard,created_y')->count();
				if(empty($export)){
					$list =  Db::name('business_income')->where($where)->field('customer_idcard,created_y,SUM(business_income_amount) AS business_income_amount,SUM(pay_for_amount) AS pay_for_amount')->group('customer_idcard,created_y')->limit($start,$end)->order('customer_idcard asc')->select()->toArray();					
				}else{
					$list =  Db::name('business_income')->where($where)->field('customer_idcard,created_y,SUM(business_income_amount) AS business_income_amount,SUM(pay_for_amount) AS pay_for_amount')->group('customer_idcard,created_y')->order('customer_idcard asc')->select()->toArray();					
				}

				if(!empty($count)){
					$sum=Db::name('business_income')->where($where)->field('SUM(business_income_amount) AS business_income_amount,SUM(pay_for_amount) AS pay_for_amount')->find();
				}

				if(!empty($list)){
					foreach ($list as $key => &$value) {
						// $list[$key]['subject_name']=get_subject_name($value['subject']);
						$value['income_sum']=isset($sum['business_income_amount']) ? sprintf("%d",$sum['business_income_amount']) : '-';
						$value['payfor_sum']=isset($sum['pay_for_amount']) ? sprintf("%d",$sum['pay_for_amount'])  : '-';
						$value['business_income_amount']=sprintf("%d",$value['business_income_amount']);
						$value['pay_for_amount']=sprintf("%d",$value['pay_for_amount']);
						if(!empty($export)){
							$result[]=[
								$value['customer_idcard'],
								$value['business_income_amount'],
								$value['pay_for_amount'],
							];
						}
					}
					unset($value);
				}

				if(!empty($export)){
					$header=[
						'身份证',
						'业务所得',
						'代付费用',
					];
					if(!empty($result)){
						$result[]=[
							'合计',
							isset($sum['business_income_amount']) ? sprintf("%d",$sum['business_income_amount']) : '',
							isset($sum['pay_for_amount']) ? sprintf("%d",$sum['pay_for_amount']) : '',
						];
					}
					$file_name=$this->exportCsv($header,$result);
            		return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
				}				

			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}

	/*损益月报表*/
	function lossProfitReportMonth(){
        $where=[];
        $limit  = $this->request->post('limit', 0, 'intval');
        $offset = $this->request->post('offset', 0, 'intval');
        $page   = floor($offset / $limit) +1 ;
        $start_time = $this->request->param('start_time', '', 'strip_tags,trim');
        $end_time = $this->request->param('end_time', '', 'strip_tags,trim');
        $export = $this->request->param('export', '', 'strip_tags,trim');
        if(!empty($start_time)){
            // $where[]=['created_ym','>=',str_replace('-','', $start_time)];
            $where[]=['created_ym','=',str_replace('-','', $start_time)];
        }else{
            $where[]=['created_ym','=',date('Ym')];
        }
        // if(!empty($end_time)){
        //   	$where[]=['created_ym','<=',str_replace('-','', $end_time)];
        // }
        $limit = ($page-1) * $limit.','.$limit;
        list($start,$end)=explode(',',$limit);
        try{
            $result=[];
            $count=Db::name('profit_loss_log')->where($where)->group('subject,created_ym')->count();

            $list =  Db::name('profit_loss_log')->where($where)->field('subject,created_ym,SUM(income_amount) AS income_amount,SUM(outcome_amount) AS outcome_amount')->group('subject,created_ym')->order('subject asc')->select()->toArray();


            if(!empty($count)){
                $sum=Db::name('profit_loss_log')->where($where)->field('SUM(income_amount) AS income_sum,SUM(outcome_amount) AS outcome_sum')->find();
                $profit_loss_sum=$sum['income_sum']-$sum['outcome_sum'];
            }

            if(!empty($list)){
                foreach ($list as $key => &$value) {
                    $value['subject_name']=get_subject_name($value['subject']);
                    $value['income_amount']=$value['income_amount']!=0 ? sprintf("%d",$value['income_amount']) : '';
                    $value['outcome_amount']=$value['outcome_amount']!=0 ? sprintf("%d",$value['outcome_amount']) : '';
                    $value['income_sum']=isset($sum['income_sum']) ? sprintf("%d",$sum['income_sum']) : '0';
                    $value['outcome_sum']=isset($sum['outcome_sum']) ? sprintf("%d",$sum['outcome_sum']) : '0';
                    //$value['profit_and_loss']=$value['income_amount']-$value['outcome_amount'];
                    $value['profit_and_loss']= '';
                    $value['profit_loss_sum']=isset($profit_loss_sum) ? sprintf("%d",$profit_loss_sum) : '0';

                    if(!empty($export)){
                        $result[]=[
                            $value['subject_name'],
                            $value['income_amount'],
                            $value['outcome_amount'],
                            $value['profit_and_loss']
                        ];
                    }
                }
                unset($value);
            }

            if(!empty($export)){
                $header=[
                    '科目',
                    '收入',
                    '支出',
                    '损益',
                ];
                if(!empty($result)){
                    $result[]=[
                        '本期损益',
                        isset($sum['income_sum']) ? sprintf("%d",$sum['income_sum']) : '0',
                        isset($sum['outcome_sum']) ? sprintf("%d",$sum['outcome_sum']) : '0',
                        isset($profit_loss_sum) ? sprintf("%d",$profit_loss_sum) : '0',
                    ];
                }
                $file_name=$this->exportCsv($header,$result);
                return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
            }

        }catch(\Exception $e){
            exit($e->getMessage());
        }
        $data['rows']  = $list;
        $data['total'] = $count;
        return json($data);

	}

	/*损益年报表*/
	function lossProfitReportYear(){
        $where=[];
        $limit  = $this->request->post('limit', 0, 'intval');
        $offset = $this->request->post('offset', 0, 'intval');
        $page   = floor($offset / $limit) +1 ;
        $start_time = $this->request->param('start_time', '', 'strip_tags,trim');
        // $end_time = $this->request->param('end_time', '', 'strip_tags,trim');
        $export = $this->request->param('export', '', 'strip_tags,trim');
        if(!empty($start_time)){
            // $where[]=['created_y','>=',str_replace('-','', $start_time)];
            $where[]=['created_y','=',$start_time];
        }else{
            $where[]=['created_y','=',date('Y')];
        }
        // if(!empty($end_time)){
        //   	$where[]=['created_y','<=',str_replace('-','', $end_time)];
        // }
        $limit = ($page-1) * $limit.','.$limit;
        list($start,$end)=explode(',',$limit);
        try{
            $result=[];
            $count=Db::name('profit_loss_log')->where($where)->group('subject,created_y')->count();

            $list =Db::name('profit_loss_log')->where($where)->field('subject,created_y,SUM(income_amount) AS income_amount,SUM(outcome_amount) AS outcome_amount')->group('subject,created_y')->order('subject asc')->select()->toArray();


            if(!empty($count)){
                $sum=Db::name('profit_loss_log')->where($where)->field('SUM(income_amount) AS income_sum,SUM(outcome_amount) AS outcome_sum')->find();
                $profit_loss_sum=$sum['income_sum']-$sum['outcome_sum'];
            }

            if(!empty($list)){
                foreach ($list as $key => &$value) {
                    $value['subject_name']=get_subject_name($value['subject']);
                    $value['income_amount']=$value['income_amount']!=0 ? sprintf("%d",$value['income_amount']) : '';
                    $value['outcome_amount']=$value['outcome_amount']!=0 ? sprintf("%d",$value['outcome_amount']) : '';
                    $value['income_sum']=isset($sum['income_sum']) ? sprintf("%d",$sum['income_sum']) : '0';
                    $value['outcome_sum']=isset($sum['outcome_sum']) ? sprintf("%d",$sum['outcome_sum']) : '0';
                    //$value['profit_and_loss']=$value['income_amount']-$value['outcome_amount'];
                    $value['profit_and_loss']= '';
                    $value['profit_loss_sum']=isset($profit_loss_sum) ? sprintf("%d",$profit_loss_sum) : '0';

                    if(!empty($export)){
                        $result[]=[
                            $value['subject_name'],
                            $value['income_amount'],
                            $value['outcome_amount'],
                            $value['profit_and_loss']
                        ];
                    }
                }
                unset($value);
            }

            if(!empty($export)){
                $header=[
                    '科目',
                    '收入',
                    '支出',
                    '损益',
                ];
                if(!empty($result)){
                    $result[]=[
                        '本期损益',
                        isset($sum['income_sum']) ? sprintf("%d",$sum['income_sum']) : '0',
                        isset($sum['outcome_sum']) ? sprintf("%d",$sum['outcome_sum']) : '0',
                        isset($profit_loss_sum) ? sprintf("%d",$profit_loss_sum) : '0',
                    ];
                }
                $file_name=$this->exportCsv($header,$result);
                return json(['code'=>0,'data'=>['file_name'=>$file_name]]);
            }


        }catch(\Exception $e){
            exit($e->getMessage());
        }
        $data['rows']  = $list;
        $data['total'] = $count;
        return json($data);
	}


	/*绩效结算*/
	function performanceSettlement(){
		$id_list  = $this->request->post('idx','', 'strip_tags,trim');
		$id_arr=explode(',',$id_list);
		$where=[];
		$where[]=['status','=',7];
		$where[]=['is_jx_deal','=',2];
		$where[]=['id','in',$id_arr];
		try{
			$time=time();
            // 启动事务
            Db::startTrans();
            //查询客户信息
            $list=Db::name('customer')->where($where)->lock(true)->select()->toArray();
            //更新主表状态
            Db::name('customer')->where($where)->update(['is_jx_deal'=>1,'update_time'=>time()]);
            $data_turnover=[];
            $performance_log=[];
            foreach ($list as $key => $value) {
            	$data_turnover[]=[
            		//支出
            		'type'=>1,
            		//子类型 绩效
            		'sub_type'=>9,
            		//科目
            		'subject'=>'80',
            		//承办人id
            		'director_id'=>$value['director_id'],
            		//介绍人id
            		'introducer_id'=>$value['introducer_id'],
            		//客户id
            		'customer_id'=>$value['id'],
            		//客户身份证
            		'customer_idcard'=>$value['id_card'],
            		//绩效
            		'amount'=>empty($value['introducer_id']) ? 300 : 100,
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];
            	//绩效
            	$performance_log[]=[
            		//承办人id
            		'director_id'=>$value['director_id'],
            		//介绍人id
            		'introducer_id'=> $value['introducer_id'],
            		//客户id
            		'customer_id'=>$value['id'],  
            		//客户身份证
            		'customer_idcard'=>$value['id_card'],
            		//业绩 客户有介绍人100,否则300
            		'amount'=>empty($value['introducer_id']) ? 300 : 100,
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];

            	//损益
            	$profit_loss_log[]=[
            		//科目
            		'subject'=>'80',
            		//支出金额
            		'outcome_amount'=>empty($value['introducer_id']) ? 300 : 100,
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];            	
            }
            Db::name('turnover')->insertAll($data_turnover);
            Db::name('performance_log')->insertAll($performance_log);
            Db::name('profit_loss_log')->insertAll($profit_loss_log);
            Db::commit();
            return json(['status'=>'0','msg'=>'']);
		}catch(\Exception $e){
			Db::rollback();
			return json(['status'=>'1','msg'=>$e->getMessage()]);
		}
	}	
	/*业务所得结算*/
	function businessIncomeSettlement(){
		$data_arr  = $this->request->post('list','', 'strip_tags,trim');
		$new_data_arr=[];
		$id_arr=array_column($data_arr, 0);
		foreach ($data_arr as $key => $value) {
			$new_data_arr[$value[0]]=$value;
		}
		$where=[];
		$where[]=['status','=',7];
		$where[]=['is_sd_deal','=',2];
		$where[]=['id','in',$id_arr];
		try{
			$time=time();
            // 启动事务
            Db::startTrans();
            //查询客户信息
            $list=Db::name('customer')->where($where)->lock(true)->select()->toArray();
            //更新主表状态
            Db::name('customer')->where($where)->update(['is_sd_deal'=>1,'update_time'=>time()]);
            $data_turnover=[];
            $business_income=[];
            foreach ($list as $key => $value) {
            	$data_turnover[]=[
            		//收入
            		'type'=>2,
            		//子类型 业务所得
            		'sub_type'=>20,
            		//科目
            		'subject'=>'90',
            		//承办人id
            		'director_id'=>$value['director_id'],
            		//介绍人id
            		'introducer_id'=>$value['introducer_id'],
            		//客户id
            		'customer_id'=>$value['id'],
            		//客户身份证
            		'customer_idcard'=>$value['id_card'],
            		//业务所得
            		'amount'=>$new_data_arr[$value['id']][1],
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];
            	$data_turnover[]=[
            		//收入
            		'type'=>2,
            		//子类型 代付费用
            		'sub_type'=>30,
            		//科目
            		'subject'=>'100',
            		//承办人id
            		'director_id'=>$value['director_id'],
            		//介绍人id
            		'introducer_id'=>$value['introducer_id'],
            		//客户id
            		'customer_id'=>$value['id'],
            		//客户身份证
            		'customer_idcard'=>$value['id_card'],
            		//代付费用
            		'amount'=>$new_data_arr[$value['id']][2],
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];

            	//业务所得
            	$business_income[]=[
            		//客户id
            		'customer_id'=>$value['id'],  
            		//客户身份证
            		'customer_idcard'=>$value['id_card'],            		
            		//承办人id
            		'director_id'=>$value['director_id'],
            		//入款金额
            		'income_amount'=>$value['lbj_fee'],
            		//业务所得
            		'business_income_amount'=>$new_data_arr[$value['id']][1],
            		//代付费用
            		'pay_for_amount'=>$new_data_arr[$value['id']][2],
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),		         		           		
            	];

            	//损益
            	$profit_loss_log[]=[
            		//科目
            		'subject'=>'90',
            		//收入金额
            		'income_amount'=>$new_data_arr[$value['id']][1],
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];            	

            	//损益
            	$profit_loss_log[]=[
            		//科目
            		'subject'=>'100',
            		//收入金额
            		'income_amount'=>$new_data_arr[$value['id']][2],
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];

            }
            Db::name('turnover')->insertAll($data_turnover);
            Db::name('business_income')->insertAll($business_income);
            Db::name('profit_loss_log')->insertAll($profit_loss_log);
            Db::commit();
            return json(['status'=>'0','msg'=>'']);
		}catch(\Exception $e){
			Db::rollback();
			return json(['status'=>'1','msg'=>$e->getMessage()]);
		}
	}

	/*佣金管理*/
	function commissionManage(){
      
        if (!$this->request->isAjax()){
        	$introducer_list=Db::name('introducer')->field('id,mobile,name')->select()->toArray();
        	$this->view->assign('introducer_list',$introducer_list);
			return $this->display('record/finance/commissionManage');
		}else{
			$where=[];
			$limit  = $this->request->post('limit', 0, 'intval');
			$offset = $this->request->post('offset', 0, 'intval');
			$page   = floor($offset / $limit) +1 ;
			$id_card= $this->request->param('id_card', '', 'strip_tags,trim');
			$director_id= $this->request->param('director_id', '', 'strip_tags,trim');
			$introducer_id= $this->request->param('introducer_id', '', 'strip_tags,trim');
			$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
			$end_time = $this->request->param('end_time', '', 'strip_tags,trim');
			$where[]=['status','=',7];
			$where[]=['is_yj_deal','=',2];
			// $where[]=['introducer_id','>',0];
			//业务id
			// $director_list=Db::name('user')->where('group_id','=',config('my.director_group_id'))->column('user_id');
			// $director_list=array_merge($director_list);
            if(!empty($id_card)){
                $where[]= ['id_card','like','%'.$id_card.'%'];
            }
            if(!empty($director_id)){
                $where[]= ['director_id','=',$director_id];
            }
            if(!empty($introducer_id)){
                $where[]= ['introducer_id','=',$introducer_id];
            }

            if(!empty($start_time)){
              	$start_time.=" 00:00:00";
              	$where[]=['create_time','>=',strtotime($start_time)];
            }
            if(!empty($end_time)){
              	$end_time.=" 23:59:59";
              	$where[]=['create_time','<=',strtotime($end_time)];
            }
			$limit = ($page-1) * $limit.','.$limit;
			list($start,$end)=explode(',',$limit);

			try{
				$count=Db::name('customer')->where($where)->count();
				$list =  Db::name('customer')->where($where)->limit($start,$end)->order('create_time desc')->select()->toArray();
				// $list =  Db::name('customer')->where($where)->limit($start,$end)->order('create_time desc')->fetchSql(true)->select();
				// echo $list;exit();
				if(!empty($list)){
					//介绍人
		            $introducer_ids=array_column($list,'introducer_id');
		            $introducer_ids=array_unique($introducer_ids);
					$introducer_arr=Db::name('introducer')->where('id','in',$introducer_ids)->column('name','id');
					//业务
		            $director_ids=array_column($list,'director_id');
		            $director_ids=array_unique($director_ids);
					$director_arr=Db::name('user')->where('user_id','in',$director_ids)->column('name','user_id');
					foreach ($list as $key => $value) {
						$list[$key]['full_name']=$value['last_name'].$value['first_name'];
						// $list[$key]['work_year_month'] =$value['work_year'].'年'.$value['work_month'].'个月';
						// $list[$key]['created_time_text']=date('Y-m-d H:i:s',$value['create_time']);
						$list[$key]['created_time_text']=date('Y-m-d',$value['create_time']);
						$list[$key]['is_introducer']=!empty($value['introducer_id']) ? 1 : 2;
						$list[$key]['performance_bonus']=empty($value['introducer_id']) ? 300 : 100;
						$list[$key]['amount']=empty($value['introducer_id']) ? 0 : 1000;
						$list[$key]['introducer_name']=isset($introducer_arr[$value['introducer_id']]) ? $introducer_arr[$value['introducer_id']] : '';
						$list[$key]['director_name']=isset($director_arr[$value['director_id']]) ? $director_arr[$value['director_id']] : '';
						// if(empty($value['introducer_id']) || !in_array($value['director_id'],$director_list))
						// 	$list[$key]['amount']= 0;
						// }else{
						// 	$list[$key]['amount']=1000;
						// }
						// if(empty($value['introducer_id'])){
						// 	$list[$key]['amount']= 0;
						// }else{
						// 	$list[$key]['amount']=1000;
						// }
					}
				}
			}catch(\Exception $e){
				exit($e->getMessage());
			}
			$data['rows']  = $list;
			$data['total'] = $count;
			return json($data);
		}
	}

	/*佣金结算*/
	function commissionSettlement(){
		$data_arr  = $this->request->post('list','', 'strip_tags,trim');
		// print_r($data_arr);exit();
		$new_data_arr=[];
		$id_arr=array_column($data_arr, 0);
		foreach ($data_arr as $key => $value) {
			$new_data_arr[$value[0]]=$value;
		}
		// print_r($new_data_arr);exit();
		$where=[];
		$where[]=['status','=',7];
		$where[]=['is_yj_deal','=',2];
		$where[]=['id','in',$id_arr];
		try{
			$time=time();
            // 启动事务
            Db::startTrans();
            //查询客户信息
            $list=Db::name('customer')->where($where)->lock(true)->select()->toArray();
            //更新主表状态
            Db::name('customer')->where($where)->update(['is_yj_deal'=>1,'is_jx_deal'=>1,'update_time'=>time()]);
            $data_turnover=[];
            $commission_log=[];
            $performance_log=[];
            $profit_loss_log=[];
            foreach ($list as $key => $value) {
            	//佣金开始
            	$data_turnover[]=[
            		//支出
            		'type'=>1,
            		//子类型 佣金
            		'sub_type'=>8,
            		//科目
            		'subject'=>'70',
            		//承办人id
            		'director_id'=>$value['director_id'],
            		//介绍人id
            		'introducer_id'=>$value['introducer_id'],
            		//客户id
            		'customer_id'=>$value['id'],
            		//客户身份证
            		'customer_idcard'=>$value['id_card'],
            		//佣金
            		'amount'=>$new_data_arr[$value['id']][1],
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];

            	//佣金
            	$commission_log[]=[
            		//承办人id
            		// 'director_id'=>$value['director_id'],
            		//介绍人id
            		'introducer_id'=>$value['introducer_id'],
            		//客户id
            		'customer_id'=>$value['id'],  
            		//客户身份证
            		'customer_idcard'=>$value['id_card'],
            		//佣金
            		'amount'=>$new_data_arr[$value['id']][1],
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];

            	//损益
            	$profit_loss_log[]=[
            		//科目
            		'subject'=>'70',
            		//支出金额
            		'outcome_amount'=>$new_data_arr[$value['id']][1],
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];

            	//绩效开始
            	$data_turnover[]=[
            		//支出
            		'type'=>1,
            		//子类型 绩效
            		'sub_type'=>9,
            		//科目
            		'subject'=>'80',
            		//承办人id
            		'director_id'=>$value['director_id'],
            		//介绍人id
            		'introducer_id'=>$value['introducer_id'],
            		//客户id
            		'customer_id'=>$value['id'],
            		//客户身份证
            		'customer_idcard'=>$value['id_card'],
            		//绩效
            		'amount'=>$new_data_arr[$value['id']][2],
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];
            	//绩效
            	$performance_log[]=[
            		//承办人id
            		'director_id'=>$value['director_id'],
            		//介绍人id
            		'introducer_id'=> $value['introducer_id'],
            		//客户id
            		'customer_id'=>$value['id'],  
            		//客户身份证
            		'customer_idcard'=>$value['id_card'],
            		//业绩 客户有介绍人100,否则300
            		'amount'=>$new_data_arr[$value['id']][2],
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	];

            	//损益
            	$profit_loss_log[]=[
            		//科目
            		'subject'=>'80',
            		//支出金额
            		'outcome_amount'=>$new_data_arr[$value['id']][2],
            		//关联id
            		'related_index'=>$value['id'],
            		//创建时间
            		'created_time'=>$time,
            		//创建日期
            		'created_d'=>date('Ymd',$time),
            		//创建年
            		'created_y'=>date('Y',$time),
            		//创建年月
            		'created_ym'=>date('Ym',$time),
            	]; 

            }
            Db::name('turnover')->insertAll($data_turnover);
            Db::name('commission_log')->insertAll($commission_log);
            Db::name('profit_loss_log')->insertAll($profit_loss_log);
            Db::name('performance_log')->insertAll($performance_log);
            Db::commit();
            return json(['status'=>'0','msg'=>'']);
		}catch(\Exception $e){
			Db::rollback();
			return json(['status'=>'1','msg'=>$e->getMessage()]);
		}
	}

	/*添加其他费用*/
	function addOtherExpense(){
		if (!$this->request->isPost()){
        	$user_list=Db::name('user')->field('user_id,name')->select()->toArray();
        	$this->view->assign('user_list',$user_list);
			return $this->display('record/finance/addOtherExpense');
		}else{
            $postField = 'type,amount,note,sub_subject,user_id';
			$data = $this->request->only(explode(',',$postField),'post');
			try {
					$time=time();
					//其他支出
					$data_other_expense=[
						'amount'=>$data['amount'],
						//科目,其他支出
						'subject'=>'110',
	            		//创建时间
	            		'created_time'=>$time,
	            		//创建日期
	            		'created_d'=>date('Ymd',$time),
	            		//创建年
	            		'created_y'=>date('Y',$time),
	            		//创建年月
	            		'created_ym'=>date('Ym',$time),
	            		//备注
	            		'note'=>$data['note'],
	            		//子科目
	            		'sub_subject'=>$data['sub_subject'],
	            		//用户id
	            		'user_id'=>$data['user_id'],
	            		//操作人id
	            		'operator_id'=>session('admin.userid'),
	            		//类型
	            		'type'=>$data['type'],
					];
					//流水
	            	$data_turnover=[
	            		'type'=>$data['type'],
	            		//科目
	            		'subject'=>'110',
	            		//金额
	            		'amount'=>$data['amount'],
	            		//创建时间
	            		'created_time'=>$time,
	            		//创建日期
	            		'created_d'=>date('Ymd',$time),
	            		//创建年
	            		'created_y'=>date('Y',$time),
	            		//创建年月
	            		'created_ym'=>date('Ym',$time),
	            	];
	            	//损益
	            	$profit_loss_log=[
	            		//科目
	            		'subject'=>'110',
	            		//创建时间
	            		'created_time'=>$time,
	            		//创建日期
	            		'created_d'=>date('Ymd',$time),
	            		//创建年
	            		'created_y'=>date('Y',$time),
	            		//创建年月
	            		'created_ym'=>date('Ym',$time),
	            	];

	            	if($data['type']==1){
						$profit_loss_log['outcome_amount']=$data['amount'];
	            	}else{
	            		$profit_loss_log['income_amount']=$data['amount'];
	            	}

        		 	// 启动事务
           		 	Db::startTrans();
           		 	$id=Db::name('other_expense')->insertGetId($data_other_expense);
					$data_turnover['related_index']=$id;
					$profit_loss_log['related_index']=$id;
					Db::name('turnover')->insert($data_turnover);
					Db::name('profit_loss_log')->insert($profit_loss_log);
					Db::commit();
					return json(['status'=>'00','msg'=>'添加成功']);
			} catch (\Exception $e) {
				Db::rollback();
				return json(['status'=>'1','msg'=>$e->getMessage()]);
			}
		}
	}


	/*报表导出*/
	function reportExport(){
      
        if (!$this->request->isPost()){
			return $this->display('record/finance/reportExport');
		}else{
			try {
				$where=[];
				$limit  = $this->request->post('limit', 0, 'intval');
				$offset = $this->request->post('offset', 0, 'intval');
				$page   = floor($offset / $limit) +1 ;
				$start_time = $this->request->param('start_time', '', 'strip_tags,trim');
				// $end_time = $this->request->param('end_time', '', 'strip_tags,trim');
				$export = $this->request->param('export', '', 'strip_tags,trim');
	            // if(!empty($start_time)){
	            //   	// $where[]=['created_ym','>=',str_replace('-','', $start_time)];
	            //   	$where[]=['created_ym','=',str_replace('-','', $start_time)];
	            // }else{
	            // 	$where[]=['created_ym','=',date('Ym')];
	            // }
	           
	    //         if(!empty($start_time)){
	    //         	$start_date=$start_time.'-01 00:00:00';
	    //         	$end_date=get_lastday($start_time).' 23:59:59';
					// $where[]=['create_time','>=',strtotime($start_date)];
					// $where[]=['create_time','<=',strtotime($end_date)];
	    //         }else{
	    //         	$start_date=date('Y-m').'-01 00:00:00';
	    //         	$end_date=get_lastday(date('Y-m')).' 23:59:59';
					// $where[]=['create_time','>=',strtotime($start_date)];
					// $where[]=['create_time','<=',strtotime($end_date)];            	
	    //         }


	            if(!empty($start_time)){
	            	$start_date=$start_time.'-01 00:00:00';
	            	$end_date=get_lastday($start_time).' 23:59:59';
	            }else{
	            	$start_date=date('Y-m').'-01 00:00:00';
	            	$end_date=get_lastday(date('Y-m')).' 23:59:59';
	            }
	            unset($where);
	            //计算外交部费用 外交部 = 身份证送件费用+护照送件费用
	            $where[]=['created_ym','=',date('Ym',strtotime($start_date))];
	            $where[]=['type','=','1'];
	            $where[]=['subject','in',['10','20']];
	            $wjb_fee=Db::name('turnover')->where($where)->sum('amount');
	            $wjb_rs=Db::name('turnover')->where($where)->count('distinct customer_id');

	            unset($where);
	            //计算台湾代表处费用 代表处=身份证认证费用+护照认证费用
	            $where[]=['created_ym','=',date('Ym',strtotime($start_date))];
	            $where[]=['type','=','1'];
	            $where[]=['subject','in',['30','40']];
	            $twdbc_fee=Db::name('turnover')->where($where)->sum('amount');
	            $twdbc_rs=Db::name('turnover')->where($where)->count('distinct customer_id');

	            unset($where);
	            //计算其他支出
	            $where[]=['created_ym','=',date('Ym',strtotime($start_date))];
	            $where[]=['type','=','1'];

	            $other_expense=Db::name('other_expense')->where($where)->field('SUM(amount) AS total_amount,sub_subject')->group('sub_subject')->order('sub_subject asc')->select()->toArray();

	            unset($where);
	            //计算佣金
	            $where[]=['created_ym','=',date('Ym',strtotime($start_date))];
	            $where[]=['type','=','1'];
	            $where[]=['subject','=','70'];
	            $total_yj=Db::name('turnover')->where($where)->sum('amount');

	            unset($where);
	            //计算绩效
	            $where[]=['created_ym','=',date('Ym',strtotime($start_date))];
	            $where[]=['type','=','1'];
	            $where[]=['subject','=','80'];
	            $total_jx=Db::name('turnover')->where($where)->sum('amount');

	   			//计算收入
	   			//计算业务所得
	            unset($where);
	            $where[]=['created_ym','=',date('Ym',strtotime($start_date))];
	            $where[]=['type','=','2'];
	            $where[]=['subject','=','90'];
	            $ywsd_fee=Db::name('turnover')->where($where)->sum('amount');

	            //计算代付费用
	            unset($where);
	            $where[]=['created_ym','=',date('Ym',strtotime($start_date))];
	            $where[]=['type','=','2'];
	            $where[]=['subject','=','100'];
	            $dffy_fee=Db::name('turnover')->where($where)->sum('amount');

	            //计算损益
	            //总收入
	            unset($where);
	            $where[]=['created_ym','=',date('Ym',strtotime($start_date))];
	            $where[]=['type','=','2'];
	            $total_income=Db::name('turnover')->where($where)->sum('amount');
	            //总支出
	            unset($where);
	            $where[]=['created_ym','=',date('Ym',strtotime($start_date))];
	            $where[]=['type','=','1'];
	            $total_outcome=Db::name('turnover')->where($where)->sum('amount');

	            //损益
	            $sy=$total_income-$total_outcome;

	            //计算未入款人数
	            unset($where);
	            $where[]=['create_time','>=',strtotime($start_date)];
	            $where[]=['create_time','<=',strtotime($end_date)];
	            $where[]=['status','<',7];
	            $wrkrs=Db::name('customer_log')->where($where)->count('distinct c_id');
				$sub_total+=$wjb_fee+$twdbc_fee+$total_yj+$total_jx;;
				$base_path=app()->getRootPath().'public/export/';
				$base_path=str_replace('\\','/',$base_path);
				$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
        		$file_name=$base_path.$original_name;
        		$fp = fopen($file_name, 'w');
        		fwrite($fp,"\xEF\xBB\xBF");
				$header=['结算日',date('Y-m-d',strtotime($start_date)).'-'.date('Y-m-d',strtotime($end_date))];
				fputcsv($fp,$header);
				fputcsv($fp,['支出',"\t"]);
				fputcsv($fp,['外交部',"\t".sprintf("%d",$wjb_fee),"人数","\t".$wjb_rs]);
				fputcsv($fp,['台湾代表处',"\t".sprintf("%d",$twdbc_fee),"人数","\t".$twdbc_rs]);
				foreach ($other_expense as $key => $value) {
					$sub_total+=$value['total_amount'];
					fputcsv($fp,[$value['sub_subject'],"\t".sprintf("%d",$value['total_amount'])]);
				}
				fputcsv($fp,['佣金总数',"\t".sprintf("%d",$total_yj)]);
				fputcsv($fp,['绩效奖金总数',"\t".sprintf("%d",$total_jx)]);
				fputcsv($fp,['合计',"\t".sprintf("%d",$sub_total)]);
				fputcsv($fp,['收入',"\t"]);
				fputcsv($fp,['服务费',"\t".sprintf("%d",$ywsd_fee)]);
				fputcsv($fp,['代付费',"\t".sprintf("%d",$dffy_fee)]);
				fputcsv($fp,['损益',"\t".sprintf("%d",$sy)]);
				fputcsv($fp,['未入账总人数',"\t".$wrkrs]);
        		fclose($fp);
        		return json(['code'=>0,'data'=>['file_name'=>$original_name]]);

			} catch (\Exception $e) {
				return json(['code'=>1,'data'=>[],'msg'=>$e->getMessage()]);
			}
		}
	}


	private function exportCsv($header=[],&$list=[]){
		try {
			$base_path=app()->getRootPath().'public/export/';
			$base_path=str_replace('\\','/',$base_path);
			$original_name=date('YmdHis').mt_rand(1000,9999).'.csv';
			$file_name=$base_path.$original_name;
			$fp = fopen($file_name, 'w');
			fwrite($fp,"\xEF\xBB\xBF");
			fputcsv($fp,$header);
			foreach ($list as $key => $value) {
				foreach ($value as &$v) {
					$v="\t".$v;
				}
				unset($v);
				fputcsv($fp, $value);
			}
			fclose($fp);
			return $original_name;
		} catch (Exception $e) {
			throw $e;	
		}
	}

	function test(){
		Log::error('错误信息测试');
	}
}